package com.credit.shtl.dao.selection;/**
 * @Classname SelectionDAO
 * @Description TODO
 * @Date 2020/10/26 18:28
 * @Created by SHTL
 */

import com.credit.shtl.dao.selection.SelectionModel;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import javax.transaction.Transactional;
import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * @InterfaceName SelectionDAO
 * @Description 选课信息DAO接口
 * @Author SHTL
 * @Date 2020/10/26 18:28
 * @Version 1.0
 *
 */
public interface SelectionDAO extends JpaRepository<SelectionModel,Integer>,
        JpaSpecificationExecutor<SelectionModel> {



    /**
     * 根据当前学生账号查看当前学生已选则的学分情况，分为选修和必修
     * **/
    // 查找必修
    @Modifying
    @Query(value = "select c.kechengname,c.xuefen from curriculum_table as c,selection_table as s where c.kechengleibie='必修' and c.kechengid=s.kechengid and s.xuehao=:xuehao",nativeQuery = true)
    List<Map<String,Object>> getXueFenBixiu(@Param("xuehao") Integer xuehao);

    // 查找选修
    @Modifying
    @Query(value = "select c.kechengname,c.xuefen from curriculum_table as c,selection_table as s where c.kechengleibie='选修' and c.kechengid=s.kechengid and s.xuehao=:xuehao",nativeQuery = true)
    List<Map<String,Object>> getXueFenXuanxiu(@Param("xuehao") Integer xuehao);


    // 根据学号获取该生的选课信息，用于生成课程表
    @Modifying
    @Query(value = "select c.kechengname,c.kechengleibie,c.classroom,c.xuefen,t.name,c.startweek,c.endweek,c.classweek,c.classsection from selection_table as s,curriculum_table as c,teacher_table as t where t.zhanghu=c.kechengteacher and c.kechengid=s.kechengid and s.xuehao = :xuehao",nativeQuery = true)
    List<Map<String ,Object>> getkechengbiao(@Param("xuehao") Integer xuehao);

    // 根据id查询选课记录
    Optional<SelectionModel> findById(Integer id);

    // 删除选课表中的重复数据
    @Transactional
    @Modifying
    @Query(value = "DELETE a FROM selection_table a,selection_table b WHERE a.id > b.id AND a.kechengid=b.kechengid and a.xuehao=b.xuehao",nativeQuery = true)
    Integer deleteRepeat();


    // 根据id删除选课信息
    @Transactional
    @Modifying
    @Query(value = "delete from selection_table as a where id=:id",nativeQuery = true)
    Integer deleteById1(@Param("id") Integer id);

    // 删除原来有选课班级，编辑时取消了班级的学生已选课程
    @Transactional
    @Modifying
    @Query(value = "DELETE a from selection_table a , student_table b where a.xuehao = b.xuehao and not INSTR(:str,b.banji) and a.kechengid=:kechengid",nativeQuery = true)
    Integer deleteRepeat1(@Param("str") String str,@Param("kechengid") Integer kechengid);





    // 没有对课程名称的模糊查询条件
    @Query(value = "select a.ctime,a.kechengid,a.kechengleibie,a.kechengname,(select count(b.xuehao) from selection_table as b where a.kechengid=b.kechengid) as 'count' from curriculum_table as a where a.kechengteacher=:kechengteacher ORDER BY count DESC",nativeQuery = true)
    List<Map<String ,Object>> SelectionClass(@Param("kechengteacher") String kechengteacher);
    // 没有对课程名称的模糊查询条件，  对 课程类型为  必修 进行筛选
    @Query(value = "select a.ctime,a.kechengid,a.kechengleibie,a.kechengname,(select count(b.xuehao) from selection_table as b where a.kechengid=b.kechengid) as 'count' from curriculum_table as a where a.kechengteacher=:kechengteacher and a.kechengleibie='必修' ORDER BY count DESC",nativeQuery = true)
    List<Map<String ,Object>> SelectionClassBixiu(@Param("kechengteacher") String kechengteacher);
    // 没有对课程名称的模糊查询条件，  对 课程类型为  选修 进行筛选
    @Query(value = "select a.ctime,a.kechengid,a.kechengleibie,a.kechengname,(select count(b.xuehao) from selection_table as b where a.kechengid=b.kechengid) as 'count' from curriculum_table as a where a.kechengteacher=:kechengteacher and a.kechengleibie='选修' ORDER BY count DESC",nativeQuery = true)
    List<Map<String ,Object>> SelectionClassXuanxiu(@Param("kechengteacher") String kechengteacher);


    // 有对课程名称的迷糊查询条件
    @Query(value = "select a.ctime,a.kechengid,a.kechengleibie,a.kechengname,(select count(b.xuehao) from selection_table as b where a.kechengid=b.kechengid) as 'count' from curriculum_table as a where a.kechengteacher=:kechengteacher and a.kechengname like concat('%',:kechengname,'%') ORDER BY count DESC",nativeQuery = true)
    List<Map<String ,Object>> SelectionClassLike(@Param("kechengteacher") String kechengteacher,@Param("kechengname") String kechengname);
    // 有对课程名称的迷糊查询条件  对 课程类型为  必修 进行筛选
    @Query(value = "select a.ctime,a.kechengid,a.kechengleibie,a.kechengname,(select count(b.xuehao) from selection_table as b where a.kechengid=b.kechengid) as 'count' from curriculum_table as a where a.kechengteacher=:kechengteacher and a.kechengname like concat('%',:kechengname,'%') and a.kechengleibie='必修' ORDER BY count DESC",nativeQuery = true)
    List<Map<String ,Object>> SelectionClassLikeBiXiu(@Param("kechengteacher") String kechengteacher,@Param("kechengname") String kechengname);
    // 有对课程名称的迷糊查询条件  对 课程类型为  选修 进行筛选
    @Query(value = "select a.ctime,a.kechengid,a.kechengleibie,a.kechengname,(select count(b.xuehao) from selection_table as b where a.kechengid=b.kechengid) as 'count' from curriculum_table as a where a.kechengteacher=:kechengteacher and a.kechengname like concat('%',:kechengname,'%') and a.kechengleibie='选修' ORDER BY count DESC",nativeQuery = true)
    List<Map<String ,Object>> SelectionClassLikeXuanXiu(@Param("kechengteacher") String kechengteacher,@Param("kechengname") String kechengname);


}
